﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetStaffInTeam]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetStaffInTeam];
GO
CREATE PROCEDURE [dbo].[sproc_GetStaffInTeam] 
    @TeamID int
/*

============================================================
功能:    得到所有在@TeamID组中的人员
参数:
    @TeamID int        :    组（类）ID
============================================================

*/

AS
BEGIN
SET NOCOUNT ON

Select
		a.Staff_ID,
		a.Staff_Name,
		a.Realname,
		b.Position_name
		(CASE WHEN a.member_type = 2 THEN 1 ELSE 0 END) AS IsLeader
    From 
        (
            SELECT
					s.*,
					t.member_type
                FROM 
                    dbo.uds_staff s,
                    dbo.uds_staff_in_Team t
                WHERE 
                    s.staff_id = t.staff_id
                    and s.dimission = 0
					and t.team_id = @TeamID
        ) a,
        dbo.uds_Position b,
        dbo.uds_staff_in_position c
    Where
		c.Position_id = b.Position_id
        and a.staff_id = c.staff_id
    ORDER BY a.staff_name

END
GO